package com.hbyj.util;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.swing.text.TabableView;

import com.hbyj.db.factory.CDBConnection;
import com.hbyj.db.function.DbTools;
import com.hbyj.hbm.vo.Sketch;
import com.hbyj.hbm.vo.Wdwzforryd;
import com.hbyj.hbm.vo.Wdwzforzhdn;
import com.hbyj.hbm.vo.YjdcGpsline;
import com.hbyj.hbm.vo.YjdcGpslineforzhd;

public class CopyTblTool {

	public  Connection connSource ;
	public  Connection connTarget;
	
	public  Connection getConnSource() {
		return connSource;
	}
	public  void setConnSource(Connection connSource) {
		this.connSource = connSource;
	}
	public  Connection getConnTarget() {
		return connTarget;
	}
	public  void setConnTarget(Connection connTarget) {
		this.connTarget = connTarget;
	}
	public  boolean convertData(String sourceTblName,String targetTblName,String region){
		boolean res = false;
		Statement statSQL = null;
		Statement statDm = null;
		ResultSet rst=null;
		String str_result="'";
		try {
			if(connSource==null||connTarget==null){
				System.out.println("数据库连接未配置");
				return res;
			}
			statSQL=connSource.createStatement();
			rst=statSQL.executeQuery(getSqlStrBbTblName(sourceTblName,region));
			deleteTargetData(targetTblName);
			//取得数据表中的字段数目，类型等返回结果
	        ResultSetMetaData rsmd = rst.getMetaData();
			int columnCount = rst.getMetaData().getColumnCount(); //列的总数
			String date=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
			
			while(rst.next()){	
				str_result="'";
				int i = 1;
				if(sourceTblName.equals("YJDC_YJDC01")||sourceTblName.equals("JCDC_JCDC00")||sourceTblName.equals("JCDC_JCDC01")
						||sourceTblName.equals("JCDC_JCDC02")||sourceTblName.equals("JCDC_JCDC03")|sourceTblName.equals("JCDC_JCDC04")
						||sourceTblName.equals("JCDC_JCDC06")||sourceTblName.equals("JCDC_JCDC07")){
					 i = 2;
				}
				for(;i<columnCount+1;i++){
					/*if(rst.getMetaData().getColumnName(i).equals("TIMEFLAG")){
						str_result += date+"','";
					}else*/
					if(rst.getObject(i)!=null&&rst.getObject(i).toString().indexOf("'")>0){
						str_result += rst.getObject(i).toString().replace("'", "")+"','";
					}else 
						str_result += rst.getObject(i)+"','";
				}
				if(!str_result.equals("'")){
					str_result = str_result.substring(0, str_result.length()-2);
					str_result = str_result.replaceAll("null", "").replaceAll("NULL", "");
					
					statDm = connTarget.createStatement();
					statDm.executeUpdate("INSERT INTO "+targetTblName+" VALUES ("+str_result+")");
					// System.out.println(t);
					 //statDm.executeUpdate("INSERT INTO HBEGS_DZZH.HBEGS_DZZH.DZZH_JCYJYJXX VALUES ('588','1','1','1','1','2010-09-18 15:24:14','1')");
				}
			}
			System.out.println("数据拷贝成功：表"+targetTblName);
			res = true;
		} catch (Exception e) {
			System.out.println(str_result);
			System.out.println(e);
			System.out.println("数据拷贝错误：表"+targetTblName);
		} finally {
			try {//关闭数据库
				rst.close();
				statSQL.close();
				if(statDm!=null)
					statDm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}		
		}
		return res;
	}
	/**
	 * author cuiyi
	 * time 2012-9-11
	 * disc 删除目标表的所有数据
	 * @param tableName
	 * @return
	 */
	private  int deleteTargetData(String tableName){
		Statement statDm = null;
		int delFlg = 0;
		try{
			statDm = connTarget.createStatement();
			int t= statDm.executeUpdate("DELETE FROM "+tableName);
		}catch (Exception e) {
			System.out.println("删除表"+tableName+"失败");
		}finally{
			try {//关闭数据库
				statDm.close();
			} catch (SQLException e) {
				System.out.println("statement关闭错误");
			}	
		}
		
		return delFlg;
	}
	private  int deleteSourceData(String tableName){
		Statement statDm = null;
		int delFlg = 0;
		try{
			statDm = connSource.createStatement();
			int t= statDm.executeUpdate("DELETE FROM "+tableName);
		}catch (Exception e) {
			System.out.println("删除表"+tableName+"失败");
		}finally{
			try {//关闭数据库
				statDm.close();
			} catch (SQLException e) {
				System.out.println("statement关闭错误");
			}	
		}
		
		return delFlg;
	}
	public  boolean convertSketch(String sourceTblName,String targetTblName,String id,String time,String jbqk0102){
		boolean res = false;
		Statement statSQL = null;
		Statement statDm = null;
		ResultSet rst=null;
		String str_result="'";
		try {
			if(connSource==null||connTarget==null){
				System.out.println("数据库连接未配置");
				return res;
			}
			statSQL=connSource.createStatement();
			String sql = "select * from "+sourceTblName+" where GeoHazardID = '"+id+"' and TimeID='"+time+"' and jbqk0102='"+jbqk0102+"'";
			rst=statSQL.executeQuery(sql);
			while(rst.next()){	
				////处理大字段数据
				Blob b = rst.getBlob(2);
				int size =(int)b.length();
				InputStream in=b.getBinaryStream();
				byte[] by= new byte[size];
				BufferedReader br = new BufferedReader(new InputStreamReader(in));
			    StringBuffer buffer = new StringBuffer();
			    String line = "";
			    while ((line = br.readLine()) != null){
			      buffer.append(line);
			    }
			   
				Sketch sketch = new Sketch(null, buffer.toString(),null,rst.getObject(4).toString(),rst.getObject(5).toString(),rst.getObject(6).toString());
				DbTools.saveObj(sketch);
			}
			System.out.println("数据拷贝成功：表"+targetTblName);
			res = true;
		} catch (Exception e) {
			System.out.println(str_result);
			System.out.println(e);
			System.out.println("数据拷贝错误：表"+targetTblName);
		} finally {
			try {//关闭数据库
				rst.close();
				statSQL.close();
				if(statDm!=null)
					statDm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}		
		}
		//res = true;
		return res;
	}
	/**
	 * 拷贝灾害点我的位置到服务器
	 * @param tblName
	 * @param id
	 * @return
	 */
	public  boolean convertWdwzforzhdn(String tblName,String id,String jbqk0102){
		boolean res = false;
		Statement statSQL = null;
		Statement statDm = null;
		ResultSet rst=null;
		String str_result="'";
		int j =0;
		try {
			if(connSource==null||connTarget==null){
				System.out.println("数据库连接未配置");
				return res;
			}
			statSQL=connSource.createStatement();
			String sql = "select * from "+tblName+" where zhdbm = '"+id+"' and jbqk0102='"+jbqk0102+"'";
			rst=statSQL.executeQuery(sql);
			
			//取得数据表中的字段数目，类型等返回结果
	        ResultSetMetaData rsmd = rst.getMetaData();
			int columnCount = rst.getMetaData().getColumnCount(); //列的总数
			while(rst.next()){
				//str_result="'";
				//int i = 2;
				String hqlTmp =  " from  Wdwzforzhdn where zhdbm = '"+id+"' and dwsj='"+rst.getObject(6)+"' and jbqk0102='"+jbqk0102+"'";
				List list = (List)DbTools.findByHql(hqlTmp);
				if(list.size()>0)
					continue;
				/*for(;i<columnCount+1;i++){
					if(rst.getObject(i)!=null&&rst.getObject(i).toString().indexOf("'")>0){
						str_result += rst.getObject(i).toString().replace("'", "")+"','";
					}else 
						str_result += rst.getObject(i)+"','";
				}
				if(!str_result.equals("'")){
					str_result = str_result.substring(0, str_result.length()-2);
					str_result = str_result.replaceAll("null", "").replaceAll("NULL", "");
					statDm.executeUpdate("INSERT INTO "+tblName+" VALUES ("+str_result+")");
				}*/
				String time = rst.getObject(6).toString();
			    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			    Date date=sdf.parse(time);
			    Wdwzforzhdn zhdn = new Wdwzforzhdn(null,rst.getObject(2).toString(),rst.getObject(3).toString(),rst.getObject(4).toString(),rst.getObject(5).toString(),date,rst.getObject(7).toString());
				DbTools.saveObj(zhdn);
				j++;
			}
			System.out.println("数据拷贝成功：表"+tblName+";拷贝"+j+"记录");
			res = true;
		} catch (Exception e) {
			System.out.println(str_result);
			System.out.println(e);
			System.out.println("数据拷贝错误：表"+tblName);
		} finally {
			try {//关闭数据库
				rst.close();
				statSQL.close();
				if(statDm!=null)
					statDm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}		
		}
		return res;
	}
	/**拷贝任意我的位置到服务器
	 * @param tblName
	 * @return
	 */
	public  boolean convertWdwzforryd(String tblName){
		boolean res = false;
		Statement statSQL = null;
		Statement statDm = null;
		ResultSet rst=null;
		String str_result="'";
		int j =0;
		try {
			if(connSource==null||connTarget==null){
				System.out.println("数据库连接未配置");
				return res;
			}
			statSQL=connSource.createStatement();
			String sql = "select * from "+tblName;
			rst=statSQL.executeQuery(sql);
			
			//取得数据表中的字段数目，类型等返回结果
	        ResultSetMetaData rsmd = rst.getMetaData();
			int columnCount = rst.getMetaData().getColumnCount(); //列的总数
			while(rst.next()){
				/*str_result="'";
				int i = 2;
				for(;i<columnCount+1;i++){
					if(rst.getObject(i)!=null&&rst.getObject(i).toString().indexOf("'")>0){
						str_result += rst.getObject(i).toString().replace("'", "")+"','";
					}else 
						str_result += rst.getObject(i)+"','";
				}
				if(!str_result.equals("'")){
					str_result = str_result.substring(0, str_result.length()-2);
					str_result = str_result.replaceAll("null", "").replaceAll("NULL", "");
					statDm.executeUpdate("INSERT INTO "+tblName+" VALUES ("+str_result+")");
				}*/
				String time = rst.getObject(3).toString();
			    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			    Date date=sdf.parse(time);
			    
				Wdwzforryd ryd = new Wdwzforryd(null,rst.getObject(2).toString(),date,rst.getObject(4).toString(),rst.getObject(5).toString(),rst.getObject(6).toString());
				DbTools.saveObj(ryd);
				j++;
			}
			System.out.println("数据拷贝成功：表"+tblName+";拷贝"+j+"记录");
			deleteSourceData(tblName);///删除平板数据
			res = true;
		} catch (Exception e) {
			System.out.println(str_result);
			System.out.println(e);
			System.out.println("数据拷贝错误：表"+tblName);
		} finally {
			try {//关闭数据库
				rst.close();
				statSQL.close();
				if(statDm!=null)
					statDm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}		
		}
		return res;
	}
	/** 拷贝任意点轨迹信息到服务器端
	 * @param tblName
	 * @param id
	 * @return
	 */
	public  boolean convertGPSLINE(String tblName){
		boolean res = false;
		Statement statSQL = null;
		Statement statDm = null;
		ResultSet rst=null;
		String str_result="'";
		try {
			if(connSource==null||connTarget==null){
				System.out.println("数据库连接未配置");
				return res;
			}
			statSQL=connSource.createStatement();
			String sql = "select * from "+tblName;
			rst=statSQL.executeQuery(sql);
			
			//取得数据表中的字段数目，类型等返回结果
	        ResultSetMetaData rsmd = rst.getMetaData();
			int columnCount = rst.getMetaData().getColumnCount(); //列的总数
			int i = 0;
			while(rst.next()){
				str_result="'";
				////处理大字段数据
				Blob b = rst.getBlob(2);
				int size =(int)b.length();
				InputStream in=b.getBinaryStream();
				byte[] by= new byte[size];
				BufferedReader br = new BufferedReader(new InputStreamReader(in));
			    StringBuffer buffer = new StringBuffer();
			    String line = "";
			    while ((line = br.readLine()) != null){
			      buffer.append(line);
			    }
			    String time = rst.getObject(3).toString();
			    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			    Date date=sdf.parse(time);
			    YjdcGpsline gpsLine = new YjdcGpsline(null, buffer.toString(),date,rst.getObject(4).toString());
				DbTools.saveObj(gpsLine);
				i++;
			}
			System.out.println("数据拷贝成功：表"+tblName+";拷贝"+i+"记录");
			deleteSourceData(tblName);///删除平板数据
			res = true;
		} catch (Exception e) {
			System.out.println(str_result);
			System.out.println(e);
			System.out.println("数据拷贝错误：表"+tblName);
		} finally {
			try {//关闭数据库
				rst.close();
				statSQL.close();
				if(statDm!=null)
					statDm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}		
		}
		return res;
	}
	public  boolean convertGPSLINEZHD(String tblName,String id,String jbqk0102){
		boolean res = false;
		Statement statSQL = null;
		Statement statDm = null;
		ResultSet rst=null;
		String str_result="'";
		try {
			if(connSource==null||connTarget==null){
				System.out.println("数据库连接未配置");
				return res;
			}
			statSQL=connSource.createStatement();
			String sql = "select * from "+tblName+" where zhdbh ='"+id+"' and jbqk0102='"+jbqk0102+"'";
			rst=statSQL.executeQuery(sql);
			//deleteTargetData(tblName);///删除平板数据
			//取得数据表中的字段数目，类型等返回结果
	        ResultSetMetaData rsmd = rst.getMetaData();
			int columnCount = rst.getMetaData().getColumnCount(); //列的总数
			int i =0;
			while(rst.next()){
				String time = rst.getObject(3).toString();
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			    Date date=sdf.parse(time);
				String sqlTmp =  "from YjdcGpslineforzhd where zhdbh='"+id+"' and time='"+time+"' and jbqk0102='"+jbqk0102+"'";
				List list = (List)DbTools.findByHql(sqlTmp);
				if(list.size()>0)
					continue;
				////处理大字段数据
				Blob b = rst.getBlob(2);
				int size =(int)b.length();
				InputStream in=b.getBinaryStream();
				byte[] by= new byte[size];
				BufferedReader br = new BufferedReader(new InputStreamReader(in));
			    StringBuffer buffer = new StringBuffer();
			    String line = "";
			    while ((line = br.readLine()) != null){
			      buffer.append(line);
			    }
			    YjdcGpslineforzhd gpsLine = new YjdcGpslineforzhd(null, buffer.toString(),date,rst.getObject(4).toString(),rst.getObject(5).toString(),rst.getObject(6).toString());
				DbTools.saveObj(gpsLine);
				i++;
			}
			System.out.println("数据拷贝成功：表"+tblName+";拷贝"+i+"记录");
			res = true;
		} catch (Exception e) {
			//System.out.println(str_result);
			System.out.println(e);
			System.out.println("数据拷贝错误：表"+tblName);
		} finally {
			try {//关闭数据库
				rst.close();
				statSQL.close();
				if(statDm!=null)
					statDm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}		
		}
		return res;
	}
	/**
	 * author cuiyi
	 * time 2012-12-11
	 * disc 根据表名称返回select语句。主要是为了增加行政区划条件以及排除自动增长的字段
	 * @param tblName 表名称
	 * @param region 行政区划编码 000000
	 * @return
	 */
	private String getSqlStrBbTblName(String tblName,String region){
		String sql = "select * from "+tblName;
		String condition = "";
		switch(tblNameList.valueOf(tblName)){
		case JCDC_JCDC00:
			condition =" where jcdc00001 like '"+region+"%'";
			break;
		case JCDC_JCDC01:
			condition =" where jcdc01001 like '"+region+"%'";
			break;
		case JCDC_JCDC02:
			condition =" where jcdc02001 like '"+region+"%'";
			break;
		case JCDC_JCDC03:
			condition =" where jcdc03001 like '"+region+"%'";
			break;
		case JCDC_JCDC04:
			condition =" where jcdc04001 like '"+region+"%'";
			break;
		case JCDC_JCDC06:
			condition =" where jcdc06001 like '"+region+"%'";
			break;
		case JCDC_JCDC07:
			condition =" where jcdc07001 like '"+region+"%'";
			break;
		case YJDC_YJDC01:
			condition =" where yjdc01000 like '"+region+"%'";
			break;
		case FZYA_FZYA03:
			condition =" where fzya03000 like '"+region+"%'";
			break;
		case QCQF_QCQF01:
			condition =" where qcqf0100 like '"+region+"%'";
			break;
		case ZDZQ_ZDZQ01:
			condition =" where zdzq01000 like '"+region+"%'";
			break;
		case JBQK01:
			condition =" where jbqk0100 like '"+region+"%'";
			break;
		}
		return sql+condition;
		
	}
	public enum tblNameList {  
		JCDC_JCDC00,JCDC_JCDC02,JCDC_JCDC01,JCDC_JCDC03,JCDC_JCDC07,JCDC_JCDC04,JCDC_JCDC06,YJDC_YJDC01,FZYA_FZYA03,QCQF_QCQF01,ZDZQ_ZDZQ01,JBQK01
  } 
}
